package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.*;

public class DBEquipment implements IFDBEquipment
{
	private Connection db_con;
	
	public DBEquipment()
	{
		 db_con = DBConnection1.getInstance().getDBcon();
	}
	
	private Equipment buildEquipment(ResultSet results)	
    {
		Equipment equipmentObj=new Equipment();		
		
		try
		{
			equipmentObj.setID(results.getInt(1));
			equipmentObj.setType(results.getString(2));
			equipmentObj.setDescription(results.getString(3));
			System.out.println("Build equipment" + equipmentObj.getName());
		}
		catch (Exception e)
        {
            System.out.println("error in building the Equipment object");
        }
        
        return equipmentObj;		
    }
	
	private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Product as p JOIN Equipment as e ON p.ProdID=e.ProdID";
        
        if (wClause.length() > 0)
        {
            query = query + " WHERE " + wClause;
        }
        
        return query;
    }
	
	//use singleWhere to select only one equipment at a time
    private Equipment singleWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        Equipment equipmentObj = new Equipment();
        
        String query = buildQuery(wClause);
        System.out.println(query);
        
        try
        {// read the equipment from the database
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            if (results.next())
            {
                equipmentObj = buildEquipment(results);
                stmt.close();
                if(retrieveAssociation)
                {//the supplier is to be built as well
                	IFDBSupplier dbSupplier=new DBSupplier();
                	Supplier suppObj=dbSupplier.getSupplierByName(equipmentObj.getSupplier().getName(), false);
                	System.out.println("Supplier is selected.");
                	equipmentObj.setSupplier(suppObj); //supplier is selected
                }
                else
                {
                	equipmentObj=null;
                }
            }
        }
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return equipmentObj;
    }
    
    private ArrayList<Equipment> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Equipment> equipmentList = new ArrayList<Equipment>();

        String query = buildQuery(wClause);

        try 
        {
        	Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            
            while (results.next())
            {
                Equipment equipmentObj = new Equipment();
                equipmentObj = buildEquipment(results);
                equipmentList.add(equipmentObj);
            }
            stmt.close();
            if(retrieveAssociation)
            {//the supplier is to be built as well
            	IFDBSupplier dbSupp=new DBSupplier();
            	for(Equipment equipmentObj : equipmentList)
            	{
            		int SuppID=equipmentObj.getSupplier().getId();
            		Supplier suppObj=dbSupp.getSuppById(SuppID, false);
            		equipmentObj.setSupplier(suppObj);
            		System.out.println("Supplier is selected.");
                }
            }
        }
        catch (Exception e)
        {
        	System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return equipmentList;
    }

	@Override
	public ArrayList<Equipment> getAllEquipment(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public ArrayList<Equipment> searchByType(String type, boolean retrieveAssociation)
	{
		String wClause=" e.Type like '%" + type + "%'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public ArrayList<Equipment> searchByDescription(float description, boolean retrieveAssociation)
	{
		String wClause=" e.Decription like '%" + description + "%'";
		return miscWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertEquipment(Equipment equipment) throws Exception
	{
		int result = -1;
        String query = "INSERT INTO Equipment(Type, Description) VALUES ('" + 
		//equipment.getID() + "', '" +
        		equipment.getType() + "', '" +
		equipment.getDescription() + "')";
        
        System.out.println("insert: " + query);
        
        try
        {
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        
        catch (SQLException ex)
        {
            System.out.println("Equipment item not created.");
            throw new Exception("Equipment item has not been inserted correctly.");
        }
        
        return result;
	}

	@Override
	public int updateEquipmentByID(Equipment equipment)
	{
		Equipment equipmentObj=equipment;
		
		int result=-1;
		
		String query= "UPDATE Equipment SET " +
		"Type= '" + equipmentObj.getType() + "' " +
		"Description= '" + equipmentObj.getDescription() + "'" +
		"WHERE ProdID= '" + equipmentObj.getID() + "'";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = db_con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	result = stmt.executeUpdate(query);
	 	 	stmt.close();
		}
		
		catch(Exception ex)
		{
	 	 	System.out.println("Update exception in Equipment db: "+ex);
	  	}
		return(result);
	}

	@Override
	public int deleteEquipmentByID(int id)
	{
		int result = -1;
    	String query = "DELETE FROM Equipment WHERE ProdID= '" + id + "'";
        System.out.println(query);
        try
        {//delete from Equipment an equipment item
            Statement stmt = db_con.createStatement();
            stmt.setQueryTimeout(5);
            result = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex)
        {
            System.out.println("Delete exception in Equipment db: " + ex);
        }
        return result;
	}

	@Override
	public Equipment getEquipmentByID(int id, boolean retrieveAssociation)
	{
		String wClause = " ProdID = '" + id + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

}
